USE [master]
GO
CREATE DATABASE BlueM
GO
USE BlueM
GO
CREATE TABLE Employee
       (
        EmployeeID INT IDENTITY PRIMARY KEY,
        EmployeeName NVARCHAR(50) NOT NULL,
        EmployeeSex BIT NOT NULL,
        EmployeeBirthday DATE NOT NULL,
        EmployeePosition NVARCHAR(50) NOT NULL,
        EmployeePhone VARCHAR(50) NOT NULL,
        EmployeeEmail VARCHAR(50) NULL,
        EmployeeAddress NVARCHAR(100) NOT NULL,
        EmployeeCity NVARCHAR(50) NOT NULL,
        EmployeeCountry NVARCHAR(50) NOT NULL,
        EmployeeJobTitle NVARCHAR(50) NOT NULL,
        EmployeeImage IMAGE NOT NULL,
        EmployeeAccount VARCHAR(50) NULL,
        EmployeePassword VARCHAR(50) NULL,
        EmployeeAvailable BIT NOT NULL
       )
GO
CREATE TABLE Customer
       (
        CustomerID INT IDENTITY PRIMARY KEY ,
        CustomerName NVARCHAR(50) NOT NULL,
        CustomerIdentifyNo VARCHAR (20) NULL,
        CustomerPhone VARCHAR(50) NULL,
        CustomerEmail VARCHAR(50) NULL,
        CustomerAccount VARCHAR(20) NULL,
        CustomerSex BIT NOT NULL,
        ModDay DATETIME NOT NULL
       )

GO
CREATE TABLE EquipmentDetail
       (
        EquipmentDetailID INT IDENTITY
                              PRIMARY KEY,
        EquipmentName NVARCHAR(50) NOT NULL,
        EquipmentPrice DECIMAL(11,2) NOT NULL,
        Available BIT NOT NULL
       )

GO
CREATE TABLE Promotion
       (
        PromotionID INT IDENTITY
                        PRIMARY KEY,
        PromotionStart DATETIME NOT NULL,
        PromotionEnd DATETIME NOT NULL,
        PromotionTitle NVARCHAR(50) NOT NULL,
        PromotionDescription VARCHAR(500) NULL,
        PromotionBanner IMAGE NULL
       )

CREATE TABLE SupplyDetail
       (
        SupplyDetailID INT IDENTITY
                           PRIMARY KEY,
        SupplyName NVARCHAR(50) NOT NULL,
        SupplyPrice DECIMAL(11,2) NOT NULL,
        Unit NVARCHAR(50) NOT NULL,
        Available BIT NOT NULL
       )

GO
CREATE TABLE RoomType
       (
        RoomTypeID INT IDENTITY
                       PRIMARY KEY,
        RoomTypeTitle VARCHAR(20) NOT NULL,
        RoomRank VARCHAR(50) NOT NULL,
        RoomPrice DECIMAL(11,2) NOT NULL,
        Bath BIT NOT NULL,
        [View] BIT NOT NULL,
        Balcony BIT NOT NULL
       )

CREATE TABLE Room
       (
        RoomID INT PRIMARY KEY,
        RoomTypeID INT NOT NULL,
        Status INT NOT NULL,
        CONSTRAINT FK_Room_RoomType FOREIGN KEY (RoomTypeID) REFERENCES RoomType (RoomTypeID)
       )
GO

CREATE TABLE PromotionDetail
       (
        PromotionDetailID INT IDENTITY
                              PRIMARY KEY,
        PromotionID INT NOT NULL,
        PromotionContent NVARCHAR(1000) NOT NULL,
        PromotionImage IMAGE NULL,
        CONSTRAINT FK_PromitionDetail_Promotion FOREIGN KEY (PromotionID) REFERENCES Promotion (PromotionID)
       )
GO

CREATE TABLE EmployeeLog
       (
        LogID INT IDENTITY
                  PRIMARY KEY,
        LoginTime DATETIME NOT NULL,
        ServiceName VARCHAR(40) NOT NULL,
        EmployeeID INT NOT NULL,
        CONSTRAINT FK_EmployeeLog_Employee FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID)
       )
GO

CREATE TABLE Equipment
       (
        EquipmentID INT IDENTITY
                        PRIMARY KEY,
        EquipmentDetailID INT NOT NULL,
        RoomTypeID INT NOT NULL,
        CONSTRAINT FK_Equipment_EquipmentDetail FOREIGN KEY (EquipmentDetailID) REFERENCES EquipmentDetail (EquipmentDetailID),
        CONSTRAINT FK_Equipment_RoomType FOREIGN KEY (RoomTypeID) REFERENCES RoomType (RoomTypeID)
       )
GO

CREATE TABLE Booking
       (
        BookingID INT IDENTITY
                      PRIMARY KEY,
        BookingDate DATETIME NOT NULL,
        CustomerID INT NOT NULL,
        TotalPayment DECIMAL(11,2) NULL,
        CONSTRAINT FK_Booking_Customer FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
       )

GO
CREATE TABLE CheckIn
       (
        CheckInID INT IDENTITY
                      PRIMARY KEY,
        BookingID INT NULL,
        RoomID INT NOT NULL,
        FeedBack NVARCHAR(500) NULL,
        CheckInDate DATETIME NOT NULL,
        CheckInEmployee INT NULL,
        CheckOutDate DATETIME NOT NULL,
        CheckOutEmployee INT NULL,
        GuestInfo VARCHAR(400) NULL,
        Payment DECIMAL(11,2) NULL,
        Available BIT NOT NULL,
        CONSTRAINT FK_CheckIn_Booking FOREIGN KEY (BookingID) REFERENCES Booking (BookingID),
        CONSTRAINT FK_CheckIn_Room FOREIGN KEY (RoomID) REFERENCES Room (RoomID),
        CONSTRAINT FK_CheckIn_Employee1 FOREIGN KEY (CheckInEmployee) REFERENCES Employee (EmployeeID),
        CONSTRAINT FK_CheckIn_Employee2 FOREIGN KEY (CheckOutEmployee) REFERENCES Employee (EmployeeID)
       )
GO

CREATE TABLE Supply
       (
        SupplyID INT IDENTITY
                     PRIMARY KEY,
        SupplyDate DATETIME NOT NULL,
        CheckInID INT NOT NULL,
        SupplyDetailID INT NOT NULL,
        SupplyNumber INT NOT NULL,
        CONSTRAINT FK_Supply_CheckIn FOREIGN KEY (CheckInID) REFERENCES CheckIn (CheckInID),
        CONSTRAINT FK_Supply_SupplyDetail FOREIGN KEY (SupplyDetailID) REFERENCES SupplyDetail (SupplyDetailID)
       )
GO

CREATE VIEW vSearchRoom
AS
       SELECT   dbo.Room.RoomID,
                dbo.Room.Status,
                dbo.RoomType.RoomTypeID,
                dbo.RoomType.RoomTypeTitle,
                dbo.RoomType.RoomPrice,
                dbo.CheckIn.CheckInDate,
                dbo.CheckIn.CheckOutDate,
                dbo.CheckIn.Available,
                dbo.RoomType.Balcony,
                dbo.RoomType.Bath,
                dbo.RoomType.[View]
       FROM     dbo.CheckIn
                INNER JOIN dbo.Room ON dbo.CheckIn.RoomID = dbo.Room.RoomID
                INNER JOIN dbo.RoomType ON dbo.Room.RoomTypeID = dbo.RoomType.RoomTypeID
GO

CREATE VIEW vEmployeeSubDetail
AS
       SELECT   dbo.Employee.EmployeeID,
                dbo.Employee.EmployeeName,
                dbo.Employee.EmployeeSex,
                dbo.Employee.EmployeePosition,
                dbo.Employee.EmployeePhone,
                dbo.Employee.EmployeeEmail,
                dbo.Employee.EmployeeJobTitle,
                dbo.Employee.EmployeeAvailable
       FROM     dbo.Employee
GO

CREATE VIEW vRoomInfo
AS
       SELECT   dbo.Room.RoomID,
				dbo.RoomType.RoomTypeID,
                dbo.RoomType.RoomTypeTitle,
                dbo.RoomType.RoomRank,
                dbo.RoomType.Bath,
                dbo.RoomType.[View],
                dbo.RoomType.Balcony,
                dbo.RoomType.RoomPrice,
                dbo.Room.Status
       FROM     dbo.Room
                INNER JOIN dbo.RoomType ON dbo.Room.RoomTypeID = dbo.RoomType.RoomTypeID
GO

GO
CREATE VIEW [dbo].[vSubPromotion]
AS
       SELECT   PromotionID,
                PromotionStart,
                PromotionEnd,
                PromotionTitle
       FROM     dbo.Promotion

GO

CREATE VIEW [dbo].[vSubPromotionDetail]
AS
       SELECT   PromotionDetailID,
                PromotionID,
                PromotionContent
       FROM     dbo.PromotionDetail

GO
